Tech Arsenal 1

home *** CD-ROM | disk | FTP | other *** search

/ Tech Arsenal 1 / Tech Arsenal (Arsenal Computer).ISO / tek-03 / 123w20.zip / 123WRITE.DOC < prev next >

Wrap

Text File | 1990-11-15 | 162KB | 4,456 lines

OVERVIEW 1 FILES INCLUDED WITH 123W20.ZIP 2 LICENSE 2 DISTRIBUTION 3 REGISTRATION 3 SUPPORT 3 REQUIREMENTS 5 GENERATING THE 123-WRITE LIBRARIES 5 MAKENEAR.BAT 6 MAKEFAR.BAT 7 COMPILING 8 CALLING SEQUENCE 9 DEFAULT SETTINGS-lFileOpen 12 DEFAULT SETTINGS-lFileAppend 13 SOME GUIDELINES 14 FINDING YOUR WAY AROUND THE SPREADSHEET 15 WHAT'S NEW? 15 lBoolean 18 lCellFunction 19 lCellMath 23 lClose 24 lCol 25 lColFunction 26 lConstMath 27 lDate 28 lDown 29 lFileAppend 30 lFileOpen 32 lGetLoc 33 lGlobal 34 lHideCol 35 lLeft 36 lLineFeed 37 lLink 38 lMultiCellMath 40 lPrintBorders 43 lPrintMargins 44 lPrintRange 45 lPrintSetup 46 lPrintTitle 47 lRange 48 lRight 49 lRowFunction 50 lSetCellForm 51 lSetCol 53 lSetCols 54 lSetDateForm 55 lSetLoc 56 lSetStrForm 57 lSetTimeForm 58 lSortKey 59 lSortRange 60 lTime 61 lTimeNow 62 lToday 63 i lUp 64 lWriteErr 65 lWriteInt 66 lWriteNum 66 lWriteStr 67 ERRORS 68 CONTACTING the author 69 APPENDIX A - DOS Error Codes 70 Routine Reference 71 ii 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought OVERVIEW 123-Write is a collection of assembly language sub programs to write a Lotus 1-2-3 file from within a QB 4+, QBX, BASIC 6.0 or BASIC 7.0+ program. Not all of 1-2-3's functions and operations are available, yet I feel that I've included some of the most frequently needed. In the past I've always written programs that allow the user to specify where report output should be directed, the screen, printer a print image file, etc. These routines were an out- growth of two needs. The first one was just wanting to "dress up" my code a little more, and add a little versatility. What could be more polished than writing a report's output directly to a 1-2-3 spreadsheet file? The second, and more important reason, was all the work involved in 1-2-3 to import, transfer and work with data from another program. One commercial package offers a "Lotus 1-2-3" export option on their report menu. When I needed just that ability once I was a more then a little disappointed to find the output file nothing more then a comma delimited sequential file. Im- porting to Lotus was simple, but dates became strings, and all the data had to be shifted around within Lotus to be of any use. Writing a macro to handle the job was a chore, and it was quite slow doing conversions of ASCII formatted dates etc. 123-Write will provide you with the ability to take that comma delimited file and convert it directly into a formatted worksheet for example. Read and write the data just the way you need it. Generate an up to date spreadsheet from your own BASIC data files whenever needed. You won't have to write out an ASCII sequential file, and then write Lotus 1-2-3 Macros to read the data file into your worksheet, and reformat it. Lotus, and 1-2-3 are registered trademarks of Lotus Development Corporation, and Microsoft is a registered trademark of Microsoft Corporation, by the way. 1 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought FILES INCLUDED in the 123W20.ZIP: 123W20.LIB Skeleton .LIB file for all BASIC programs - EXCEPT - BC 7.0+ with far strings. 123W20F.LIB Skeleton .LIB file for BC 7.0+ programs with - FAR STRINGS ONLY - LCOLS.BAS Source code interface for lCols routine. LDATE.BAS Source code interface for lDate routine. LTIME.BAS Source code interface for lTime routine. MAKENEAR.BAT Compiles the .BAS files, adds them to 123W20.LIB, generates the non-QBX 123W20.QLB file for QB 4.0+. MAKEFAR.BAT Compiles the .BAS files, adds them to 123W20F.LIB, generates the QBX 123W20F.QLB QuickLib file. 123WRITE.DOC Complete documentation for version 2.0 123WRITE.BI An ASCII include file that DECLARES all the 123-Write routines. 123DEMO2.BAS Demonstration of a few of the routines. example: QB 123DEMO2 /L 123W20.QLB or: QBX 123DEMO2 /L 123W20F.QLB The .LIB files are "skeletons" because they contain all the assembly language routines, but do not contain the three BASIC routines. The MAKENEAR and MAKEFAR batch files handle the com- piling, insertion in the object file library and building of the QuickLibrary for you. LICENSE 123-Write is not PUBLIC DOMAIN software. It is distributed as Shareware, and as such, non registered users are granted a li- cense to use 123-Write for a limited evaluation period, for your own noncommercial use. Use of the routines in this archive are not to be used in any software that you sell, offer for sale, generate in the course of your employment, or after a reasonable evaluation period (30 days). All that requires registering the copy. 2 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought DISTRIBUTION You may copy and freely distribute 123-Write, as long as: No fee is charged. (Recognized user groups and clubs may charge a nominal fee for expenses.) All the files included within this archive are distributed intact and without any modification(s). WARRANTY This software is distributed AS IS without warranty of any kind, express or implied, including but not limited to implied warran- ties for the software's quality, performance, merchantability, or fitness for any particular purpose. Neither myself or any dealer or distributor shall be liable with respect to any liability, loss, or damage caused, or alleged to be caused directly or indirectly by this software. REGISTRATION If you use the 123-Write routines within any application that you distribute, or in the course of your employment, registration at $25.00 is mandatory. If you find 123-Write convenient to use, if you find 123-Write a help to your programming, a $25.00 registra- tion is expected. With each registration you will receive a diskette with a li- censed (distributable) copy of the library, be listed to receive a diskette of the next release (if any) of 123-Write, and receive what you may possibly find an interesting text file on the Lotus worksheet file format. (perhaps you may be interested in reading a worksheet file before I get finished with 123-READ, but that's another story.) SUPPORT Questions will be answered when asked. Fastest method for non- registered users may be via a call to The Off Hour Rockers BBS in Sayreville NJ. 201-727-6917 and 201-727-6785. Settings are N81 9600/2400/1200 baud 24 hours a day. Join the BASIC conference (j;7) after logging on, and address your message to Tom Vought and I'll reply as soon as possible. The BBS is founded on free spirit principles and is a little off the wall at times, no hassle about signing on or downloads. Please be sure to use you real name though, as "handles" are considered quite tacky. The latest version of 123-Write is always available for download from conference 7. CIS ID: 70054,1367 (Infrequent, so use CIS E-Mail) US Mail to the address on the registration form will also work reasonably well. A registration form for your use follows. 3 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought Please make checks payable and mail To: Thomas J. Vought 5 John Street Morganville, NJ 07751 Registration of: 123-Write ver 2.0 Name: _____________________________________________ Company Name: _____________________________________________ Mailing Address Street: _____________________________________________ Town, State & Zip: _____________________________________________ Phone: ____________________________________________ Do you have a modem? ______________ Disk format ____ 3.50" ____ 5.25" Where did you receive 123-Write from? (Just curious.) What functions, @formulas, or other esoteric stuff would you like to see included in any future releases of 123-Write? Comments?: 4 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought OK, I think I have all that stuff about registration out of the way. Only one more item. Routines are added based on user suggestions, registered or not. Requirements: 123-Write (2.0) was developed and tested using Microsoft's Macro Assembler ver 5.1, Microsoft's Basic Compiler versions 6.0 and 7.1 and both QB ver 4.5 and QBX ver 7.1. The worksheet files written are compatible with all current versions of Lotus 1-2-3. Specific features of Release 3 are not supported, though the spreadsheets written can be loaded by Re- lease 3 without problem. Care should be taken to avoid calling routines that are not supported by the target Lotus version. I've tried to make note of Lotus version requirements in the routine index and in the routine descriptions. If I missed or made a blatantly greivious error.... Sorry, let me know and I'll correct it in the next revision of the documentation. 123-Write version 2.0 requires DOS version 2.0+. GENERATING THE 123-WRITE LIBRARIES Creating a quick library and a linker library is performed by either the MAKENEAR.BAT or MAKEFAR.BAT files (or both) included with the zip file. MAKENEAR (page 6) is for: QB 4.0, QB 4.5 and BC 6.0 MAKEFAR (page 7) is for: BC 7.0 near and far string support. BC 7.1 near and far string support. QBX 7.0 QuickLibrary QBX 7.1 QuickLibrary The preferred method is to work in a sub-directory that has only the 123-Write files in it. For either batch file, make the 123-Write directory the default. Access to: LIB.EXE LINK.EXE and your basic compiler (BC.EXE) should be assured via your PATH statement. The LINKer will need your quick library support module, BQLBxx.LIB or QBXQLB.LIB either in the same directory, or pointed to by the LIB= environment variable. 5 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought If your development environment is QB 4.0, QB 4.5 or BC 6.0: 1. Ignore the MAKEFAR.BAT batch file entirely. 2. The syntax for MAKENEAR is: MAKENEAR compilerName qlbSupportLib [compiler / options] This will create both the QB Quick Library, 123W20.QLB and 123W20.LIB. The compilerName variable is usually BC, unless you keep various versions of the compiler on your disk, as I do. Either way you must enter a compiler name. The qlbSupportLib variable is the BQLBxx.LIB file that was sup- plied with whatever version of the compiler you are using. There are a few out there for QB 4.0, so double check. QB 4.5 users should specify BQLB45.LIB. If I remember correctly, BC 6.0 was supplied with BQLB41.LIB. Enter any compiler switches you usually use in one string if needed. Use /FPI/O instead of /FPI <space> /O. If you want to generate multiple libraries for different compiler switch set- tings, feel free to run the batch file multiple times, renaming the resulting .LIB and .QLB file each time. If 123W20.LIB or 123W20.QLB is found in the current directory when the batch file is started, a reminder is printed to move/delete them and the batch file terminates. The batch file will compile the BASIC source code files then add them to the 123W20.LIB with LIB.EXE. After 123W20.LIB is updated with the three BASIC object files, LINK is invoked to create the 123W20.QLB QuickLibrary. 6 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought BASIC 7.0+ users should run the MAKEFAR batch file only. It will generate two .LIB files (123W20.LIB and 123W20F.LIB) and one .QLB file, 123W20F.QLB. The syntax is: MAKEFAR compilerName [compiler / options] The compilerName variable is usually BC, unless you keep various versions of the compiler on your disk, as I do. The MAKEFAR batch file will use the /FS and /FPI switches auto- matically for 123W20F.LIB and the 123W20F.QLB files. Any other switches can be added except for /FS, but use care when choosing the switches. The switches need to be entered without any spaces separating them, use /O/T instead of /O <space> /T. The batch file will compile the BASIC source code files for near string use with any switches you supply, then add them to the 123W20.LIB with LIB.EXE. After 123W20.LIB is updated with the three BASIC near string object files, the compiler is invoked again with the /FS/FPI switches, plus any you supply, to update the 123W20F.LIB with the three BASIC source code files. Lastly, LINK is invoked to create the 123W20F.QLB from 123W20F.LIB. If you do not supply any switches for the compiler when invoking the batch file, you will have: 123W20.LIB Default BASIC options of: /FPI and near strings. [You should be able to link with /O code also.] 123W20F.LIB Far string, /FPI. 123W20F.QLB Far string, /FPI. One note concerning arrays in EMS memory, the /EA option with QBX. I am not sure how the 123-Write routines that access arrays directly in memory will behave. My belief is that when you pass the segment and offset of the selected array element to a library routine, the array should be moved by QBX into addressable memo- ry, the EMS page frame, if it's out in never-never land somewhere before the CALL is invoked. Then the segment portion of the array would be somewhere above A000hex, and a 16K boundary shouldn't be crossed. There doesn't seem to be any firm documen- tation on this, so if your machine goes into orbit when a call to one of the array based routines is called, I'd suggest dropping the /EA switch, or making the array $STATIC. The arrays used are small enough that this shouldn't be a hardship. 7 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought Notes for both MAKENEAR and MAKEFAR: If 123W20.LIB or 123W20.QLB already exist in the current directo- ry, the batch file will stop and prompt you to remove them. The reason for this is to allow you to tailor, if needed, the library files to your working environment. Take a look at the MAKE????.BAT files and feel free to modify them if needed to your particular needs. Just don't pass on the 123-Write package with the batch files or the .LIB files modi- fied, please! LIB will issue a warning message that the BASIC object files are not in the library the first time you run MAKENEAR.BAT. The batch file specifies that the BASIC object files be 'replaced' in the library file, in case it is run more than once. The warning message can be ignored. If you rerun the batch file for some reason (Different compiler switches maybe.) the error message won't appear, and all will be as it should be. A note on BASIC PDS 7.0. If you haven't upgraded to 7.1 yet, please do so. No I don't own stock in Microsoft, and I do feel $50.00 for what I consider a bug fix to be outrageous, but 7.1 seems to be a much more stable product. Also, if you don't need the far string memory space, near string routines will run faster. COMPILING with 123WRITE: When compiling to an .EXE file, it's best to compile from the DOS command line, NOT from within the QuickBASIC environment. There is a switch that QB throws into the LINK command line that seems to cause errors and trashed EXE's. QBX may also mix near and far string .LIB files if you're not careful. 8 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought Sequence of 123-Write calls: There can only be one worksheet file open for writing at a time. The routines need to be called in the proper sequence: 1. Open the file, of course. Use either: lFileOpen(filename$, errCode%) Supply a fully qualified file name with the drive and path if desired. An existing file will be over-written. Or lFileAppend(filename$, errCode%) To open an existing worksheet file to add on to it. 2. Write any column width settings desired. Use either: lSetCol(column%,colWidth%) for a single column width setting. Or lSetCols(columnWidths%()) for a variety of columns. 3. Set the cell format for strings and numeric data if other then the defaults of left-aligned label cells and "default" numeric cells. See page 12 for default set ups. 4. Set the starting column and row, if other than: 0,0 (A1) if lFileOpen was used or the {end}{home} cell if lFileAppend was used. 5. Write the data. 6. Close the file. CALL lClose Closing the file is a must. If it's not done via a call to lClose, you'll get a "Part of File Missing" error from Lotus when loading the spreadsheet. Lotus needs certain information when it loads a worksheet, and the information can't be written to the file by 123-Write unless you call the lClose sub-program to close the file. If a worksheet file is not closed, the disk directory will not be updated, and a file handle will lost from DOS. The worksheet file is opened and manipulated by using DOS file access calls, not BASIC file calls. One important item to remem- ber is: If you use a CLOSE (all) statement in your program, the worksheet file WILL NOT be closed. This is just the opposite of the behavior in earlier versions of 123-Write. 9 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought When the spreadsheet file is opened, either with lFileOpen or lFileAppend, the lClose routine is logged with BASIC. What this means is that if you're working in the QuickBASIC environment, and your program terminates abnormally, lClose will be called automatically. If you make a change to your program while step- ping through it, and QuickBASIC prompts that "You'll have to restart your program", and you elect to restart the program, lClose will be called before control is passed back to you. What that means is that you should not try to write to a spreadsheet file that was opened before you made the change, by assuming it's still open and stepping 'around' a call to lFileOpen. Check your BASIC manual for a description of the B_OnExit routine, in the mixed language programming guide, for a better description of what this all means. Another item of note is range names. If your spreadsheet may be read by a word processor, or manipulated by some other commerical product, then keep the following in mind. When Lotus saves a spreadsheet to disk, it writes the spreadsheet in a consistent manner. A certain amount of data is written at the start of the file, before the cell contents. As the cells are written, they are placed in the file in a row by row, left column to right column sequence (A1, B1, C1, A2, B2, C2, A3, B3 etc). 123-Write writes as much info as needed at the start when lFileOpen is called, then as calls are made from BASIC, the specified data is appended to the file. With 123-Write, you can write almost anything anywhere you'd like in the file. Matter of fact I think that's a nice feature, and Lotus and some clone spreadsheet programs don't seem to have a problem with 'anything goes' style of files, a setting change or a repeated cell just replaces the previously read data. The problem arises when you you use a 'viewer' or try to import to a word processor. I use Wordstar, so I'll use that product as an example. When the 'other' product reads the file, it may assume that the file was written by Lotus et al. If it doesn't find range names where Lotus would have placed them, it might assume that the file has no range names. Wordstar 5.0 behaves this way. When the 123DEMO2.BAS file was written, calls to lRangeName were inter- spersed throughout hte program. As each 'page' routine was called, the range would be defined with lRangeName at the end of the SUB. When Lotus read the file in, no problem. However, when I asked Wordstar to import the file, the only option WordStar would give me was to import the whole file. No range names were presented. Changing the sequence of calls in the demo program corrected the problem. What I did was to write the "WriteRange- Names" sub program and moved all the calls into it. Then insert- ed a call to the new routine immediately after the call to lFile- Open, before any cells were written to the file. Wordstar now lists all the PAGE_xx ranges when I specify an import. 10 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought File viewers are another matter. Lotus's Magellan, I've been told, displays the file as expected, but other viewers don't display the whole file. I believe this may be because the demo writes cells in a 'some here, some there' manner, rather then the row by row, col by col sequence, and the viewer just loses it, and displays as much as it can figure out. Last item is the results of the formulas written to the file. 123-Write does not write the result of a formula to the file, as a spreadsheet would, but just writes the formula. When the file is loaded, the spreadsheet does a calculation on all cells, and you never see the zero 'results'. Not true with an import to a word processor or a file viewer. Don't be alarmed if the result of all your hard work appears to be nothing but zeros when 'viewed' or imported to a word processor. A File Retrieve, File Save sequence is all that should be needed to make your spread- sheet file more conformist. Bottom line is, I feel the flexibility afforded is more important then compatibility with products other than spreadsheet programs. A perfect example is when reading from a file to create a spread- sheet. You don't know in advance the cells that are going to be in a range, so you can't stick the range name definition at the start of the spreadsheet file. 123-Write allows you to define the range after the imput file is read and written to the spread- sheet file, once the variable boundaries of the range are known. 11 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought DEFAULT SETTINGS with lFileOpen. When a worksheet file is opened with lFileOpen, one of two condi- tions will dictate the default settings of the worksheet. Either the call is the first one to open a spreadsheet file (lFileOpen or lFileAppend) in the current program, or a subsequent call to lFileOpen in the same program: Setting: First Open: Subsequent: --------- ----------- ----------- Calc Iterations: 1 1 Calc Mode: Auto Auto Calc Order: Natural Natural Global Protection: Off Off Printing: Formatted Formatted Left margin: 4 4 Right margin: 76 76 Top Margin: 2 2 Bottom Margin: 2 2 Lines per Page: 66 66 Hidden Columns: None None Lotus Default: Cell Format: Default Default Note1 Label alignment: '(left) '(left) Note2 123-Write Default: Cell Format: Protected-Default Last setting. Label alignment: '(left) Last setting. Date Format: dd-mmm-yy (D1) Last setting. Time Format: hh:mm:ss AM/PM Last setting. NOTE1: The Lotus "Default" is a unique cell format setting. What form it takes, and the cells that are marked with it, is the format selected with the "/Worksheet Global Format" command. This format can be selected with the lSetCellForm routine. NOTE2: The Lotus "Default" label alignment, as used here, refers only to what alignment text typed into the worksheet while in Lotus will take on. It is not changeable with . The 123-Write formats determine what format 123-Write will speci- fy for cells it writes. Every cell written has a format speci- fied. The label alignment 123-Write default can be overridden by including a label prefix character in the string that is written to the cell. See lWriteStr for more information. 12 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought DEFAULT SETTINGS with lFileAppend. The first consideration here has to be the fact that the work- sheet file is set with the defaults in effect when it was last written. That could be either the way it was when you wrote it with 123-Write, or the settings made to it in Lotus 1-2-3, before it was saved to disk from Lotus. That fact precludes a list of what settings are in effect as far as global worksheet defaults. However, the following 123-Write defaults can be relied on. "First Append" refers to the first time your program opens a file (either lFileOpen or lFileAppend) for append, and "subsequent" refers to opens for appends while your program is still running, whether it's the same file or a different file: Setting: First Append: Subsequent: --------- ----------- ----------- Hidden Columns: None None 123-Write Default: Cell Format: Protected-Default Last setting. Label alignment: '(left) Last setting. Date Format: dd-mmm-yy (D1) Last setting. Time Format: hh:mm:ss AM/PM Last setting. What all this boils down to, is that the above "Last Setting" items are not changed it they've already been set, otherwise they are set to the default conditions shown. 13 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought SOME GUIDELINES: Things to remember when coding calls: All CALLs that take numeric parameters use 2 byte signed inte- gers, except for lWriteNum, which takes one double precision 8 byte real value, and lConstMath where the constant is also a double precision 8 byte real variable. All cell references in Lotus are 0 based. Column A is column 0, and row 1 is row 0. Clear as mud right? Well, just remember to subtract one from what you think of the row number as. If you count columns on your fingers like I do, the first finger is 'B'. All the routines that need column and row numbers use the Lotus zero based references. lCol is a function (remember to DECLARE it as an INTEGER function in your program if you use it.) that will return the zero based column number when called with a one or two byte string. See the entry for lCol for an example. It relieves the strain of counting on your fingers. Now an important point. The logic in these routines (if any) is based on a left to right, top to bottom sequence. This simulates the printing of a report. Every time a routine is called that writes data to a cell, the cell pointer is: Increased by one column if the write is successful, the row stays the same. May not be increased if there is an error detected. May move the pointer after an error is detected, because the Lotus function @ERR is written to the cell. Won't move the cell pointer, because there is no cell data written. lGlobal and lSetCol are examples. If you should attempt to write a cell beyond the 255th, (IV) the routines will increment the row and return to column 0 (column A). Each routines' description will describe what actions are taken for errors, out of range paramters, etc. When printing to the printer or screen, you allow for line feeds, in 123-Write, the lLineFeed routine serves the same purpose. The row is increased and the column returns to 0. Another point is when using the 'cursor movement' routines, lLeft/lRight/lUp/lDown. If you have just written to cell A5 for example, which is column 0, row 4, and call lUp(4) to move up to row 0, you will be in column B (1). The cell pointer was moved one column to the right after A5 was written. Some allowances 14 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought for the column increments upon cell writing have to be calculated in. Where Am I in this 'SpreadSheet' anyway? The answer to that depends upon how the spreadsheet file was opened. If it's a new spreadsheet file (lFileOpen) then the column pointer is set to column 0 (A) and the row pointer is set to row 0 (1) when the file is opened. The {HOME} position there- fore, is the first cell written, then 1,0 (B1), then 2,0 (C1). A CALL to lLineFeed at this point would mean that the next cell written would be 0,1 (A2), then 1,1 (B2) etc. It takes a little getting used to, but it works well. If the file was opened with lFileAppend, then the cell pointer will be set to the {end}{home} position. This cell is the bottom right corner of the spreadsheet. It may very well contain data, so the first thing after calling lFileAppend you need to do is position the cell pointer to where you want the next stream of data to be written. There are routines to get the current location of the cell point- er and to set the location of the cell pointer. First lGetLoc(whereAmIcol%, whereAmIrow%) returns the current cell pointer column and row coordinates, and then a call to lSetLoc(nextCol%, nextRow%) will adjust the cell pointer. What's new? This is not meant to be a substitute for reading the entire manual if you've used previous versions of 123-Write. I have tried to make version 2 quite a bit more comprehensive, and a lot of the calling syntax has been changed from version 1.0 and 1.1. That's why I've changed every routine's name. Three reported bugs in version 1.0 were corrected in version 1.1a/b, and or 2.0. The lDate routine choked on a date delimited by slashes. That was corrected in 1.1a/b so a dash "-" or a slash "/" was OK. New feature (??) in 2.0, is that the delimiter is ignored altogether! Calculations are based on the length of the string passed, so a little more work is on your shoulders. See the entries for both lDate and lTime for more information. The second was a MAJOR bug in the 1.0 LotusWriteStr routine. The code attempted to test the second character of the string passed to the routine {ie: MID$(data$,2,1)} when a test of the string's length hadn't verified it was at least two bytes long. A real easy one, and it shouldn't have been there, but the easy bugs are the ones you don't (at least I miss them) plan and test for. My apologies on this one. 15 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought The last one was an annoyance, the string passed to LotusWriteStr (ver 1.0) was modified if it did not contain a leading label prefix character. The routine would prefix the passed data string with the default label prefix character and that string got returned modified. The lWriteStr routine now manipulates the string internally and won't modify the string passed to it. Version 2.0: At this point I'm so muddled from converting everything over to assembly language, I'm sure I won't cover all the changes. Off the top of my head: lBoolean Sub program added. Allows you to use the Lotus #AND#, #OR#, #NOT# operators, and equal, not equal, less than greater than etc. lCellFunction Now supports all 92 of the Release 2.2 @functions. Range, data base and mixed references have been added to the cell functions. lColFunc Both routines have had the @STD and @VAR functions and added. Also, the lColFunction can use @ROWS and the lRowFunc lRowFunction can use @COLS. lConstMath Now allows you to reverse the order of the constant and cell reference. lHideCol Added to allow you specify hidden columns. lLink Added to use the Release 2.2 spreadsheet file linking feature. lPrintBorders Has been modified to allow the one cell 'A1' to be designated as a border row or border column. lSetCellForm Has been revised to allow setting of all possible cell formats, including +/-, general, hidden and default. lTimeNow Added to write the @NOW formula to a cell in a time display format. 16 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought Again, many of the parameters passed to the routines in version 2.0 have been changed, and I have to ask that you scan this manual and your code carefully for any changes. Finally, version 2.0 of 123-Write is not intended to be backward compatible with earlier versions. I attempted to keep the calling syntax as close as possible with 1.0 and 1.1 versions wherever possible. With the need for additional error information to be passed, I felt that it would be better to use different routine names altogether. Therefore, if you're upgrading, please don't attempt to use a search and replace on the "Lotus" that all the version 1.x routine names used, and leave it at that. Use the 123W20.BI file and be sure to double check the parameter passing sequences. Detailed descriptions of each routine follow in alphabetical order. All routines begin with L--- Error actions are defined at the end of each routine's descrip- tion. The changes made to the cell pointer (CP) are listed for each routine, and in each routine's error action section the action is defined as: (CP +) Cell pointer increased to next column. (CP 0) Cell pointer not increased. 17 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lBoolean COMPATIBILITY: All releases. CELL POINTER: Increased 1 column. SYNTAX: lBoolean (col1%, row1%, col2%, row2%, operation%) col1% is the column number of the first cell. row1% is the row number of the first cell. col2% is the column number of the second cell. row2% is the row number of the second cell. operation% indicates the comparison to be performed between cell 1 and cell 2. Operation codes are: 1 Equal to Cell1 = Cell2 2 Not equal to Cell1 <> Cell2 3 Less than or equal to Cell1 <= Cell2 4 Greater than or equal to Cell1 >= Cell2 5 Less than Cell1 < Cell2 6 Greater than Cell1 > Cell2 7 AND (not bitwise) Cell1#AND#Cell2 8 OR (not bitwise) Cell1#OR#Cell2 9 NOT (not bitwise) #NOT#Cell1 lBoolean requires two cells to work on, unless the NOT operator is being used. With the not operator, the first cell coordinates are used, the second column/row pair is ignored, but still must be in the call (use 0,0). Lotus will return a positive 1 in the cell if the condition is true, a zero otherwise for the comparison set (operations 1 through 6). The AND, OR, and NOT functions are not bitwise operators, the following rules are used by Lotus: AND: If both cells are not zero, returns 1 otherwise returns 0. OR: If either cell is not zero, returns 1 otherwise returns 0. NOT: If the target cell is zero (false) returns 1, otherwise will return false, 0. ERRORS: No file open: No action taken. Oper% out of range (1-9): @ERR in current cell. (CP +) 18 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lCellFunction COMPATIBILITY: Various, see table page 21. CELL POINTER: Increased 1 column. SYNTAX: lCellFunction(dSeg%, dOfs%) dSeg% is the VARSEG() of the first element of an integer array, or a user defined TYPE. dOfs% is the VARPTR() of the first element of an integer array, or a user defined TYPE. lCellFunction writes a specified Lotus @ function to the current cell. All 92 of release 2.2's @ functions are supported. Multiple functions can't be combined, the alternative is to write the components of the multiple functions to out of the way cells, and operate on them in steps. The routine expects the far address of an integer array or a user defined type that is made up of only integers. The first element indicates the function to perform. With the exception of the @CHOOSE function, the second through x elements are pairs of col/row co-ordinates. The function requested deter- mines what is to follow. Some functions operate on one or two absolute cells, some on ranges, and some mix ranges and cells. See the Lotus documentation for the correct sequences of opera- tors required for each @function. When using the @CHOOSE function, number 18, the second element indicates the number of absolute cell references that follow, then the column, row co-ordinates for each cell. When using an integer array, DIMed as array%(1 TO 12) for exam- ple, you could set up a call to write the @IF function to the current cell as: array%(1) = 29 'Function 29 is @IF( array%(2) = 0 'Col A Test cell: A1 array%(3) = 0 'Row 1 array%(4) = 2 'Col C A1 <> 0? : C4 array%(5) = 3 'Row 4 array%(6) = 4 'Col E A1 = 0? : E6) array%(7) = 5 'Row 6 CALL lCellFunction(VARSEG(array%(1)),VARPTR(array%(1)) Would result in: @IF(A1,C4,E6) in the current worksheet cell. 19 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought lCellFunction (continued) You could use a TYPE'd variable instead of an array: TYPE functionType fCode AS INTEGER col1 AS INTEGER row1 AS INTEGER col2 AS INTEGER row2 AS INTEGER col3 AS INTEGER row3 AS INTEGER col4 AS INTEGER row4 AS INTEGER END TYPE DIM func AS FunctionType func.fCode = 85 '85 = @RATE, requires 3 cells func.col1 = 0 ;Col A func.row1 = 0 ;Row 1 func.col2 = 2 ;Col C func.row2 = 3 ;Row 4 func.col3 = 4 ;Col E func.row3 = 5 ;Row 6 CALL lCellFunction(VARSEG(func),VARPTR(func)) Results in: @RATE(A1,C4,E6) being written to the current work- sheet cell. In the table on the following page, each function is listed alphabetically. The individual 123-Write function number is listed first, the Lotus @ function, then the number of cells needed, followed by the type of parameter and Lotus version. The number of cells varies by parameter type. The parameter types are variables (cells), ranges, mixed parameters or database parameters. The parameter type codes are: V a variable, which is a cell address. Requires an integer column number then an integer row for each variable. Maximum is 4, which would need an integer array of 9 elements (Function #, c1, r1,...c4, r4) R a range, which is two cell addresses, the upper left corner cell, then the lower right corner cell. Those functions that are marked R only require one range maximum, so a 5 element array will handle them: Function #, upLeftCol, upLeftRow, lowRcol, lowRrow. D a database function parameter. These take the form of input range, offset cell, criteria range, so a 10 element array is needed. 20 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought ## @Function Pass Type Ver ## @Function Pass Type Ver ------------------------------------------------------------------ 84 = @@ 1 V 20 74 = @LEFT 2 V 20 3 = @ABS 1 V 1a 40 = @LENGTH 1 V 20 15 = @ACOS 1 V 1a 7 = @LN 1 V 1a 14 = @ASIN 1 V 1a 6 = @LOG 1 V 1a 13 = @ATAN 1 V 1a 73 = @LOWER 1 V 20 12 = @ATAN2 2 V 20 54 = @MAX 1 R 1a 51 = @AVG 1 R 1a 43 = @MID 3 V 20 78 = @CELL 3 M* 20 53 = @MIN 1 R 1a 49 = @CELLPOINTER 1 V 20 36 = @MINUTE 1 V 20 44 = @CHAR 1 V 20 17 = @MOD 2 V 1a 18 = @CHOOSE ? V 1a 31 = @MONTH 1 V 1a 80 = @CLEAN 1 V 20 82 = @N 1 R 20 45 = @CODE 1 V 20 1 = @NA 0 1a 69 = @COLS 1 R 20 25 = @NOW/TODAY 0 1a 10 = @COS 1 V 1a 56 = @NPV 3 M* 1a 52 = @COUNT 1 R** 20 8 = @PI 0 1a 87 = @CTERM 3 V 20 26 = @PMT 3 V 1a 24 = @DATE 3 V 1a 77 = @PROPER 1 V 20 47 = @DATEVALUE 1 V 20 27 = @PV 3 V 1a 62 = @DAVG 5 D 1a 23 = @RAND 0 1a 30 = @DAY 1 V 1a 85 = @RATE 3 V 20 63 = @DCOUNT 5 D 1a 71 = @REPEAT 2 V 20 90 = @DDB 4 V 20 76 = @REPLACE 4 V 20 65 = @DMAX 5 D 1a 75 = @RIGHT 2 V 20 64 = @DMIN 5 D 1a 33 = @ROUND 2 V 1a 67 = @DSTD 5 D 1a 70 = @ROWS 1 R 20 61 = @DSUM 5 D 1a 81 = @S 1 R 20 66 = @DVAR 5 D 1a 37 = @SECOND 1 V 20 2 = @ERR 0 1a 9 = @SIN 1 V 1a 83 = @EXACT 2 V 20 88 = @SLN 3 V 20 16 = @EXP 1 V 1a 5 = @SQRT 1 V 1a 21 = @FALSE 0 1a 58 = @STD 1 R 1a 46 = @FIND 3 V 20 42 = @STRING 2 V 20 28 = @FV 3 V 1a 50 = @SUM 1 R 1a 60 = @HLOOKUP 4 M* 20 89 = @SYD 4 V 20 35 = @HOUR 1 V 20 11 = @TAN 1 V 1a 29 = @IF 3 V 1a 86 = @TERM 3 V 20 68 = @INDEX 4 M* 20 34 = @TIME 3 V 20 4 = @INT 1 V 1a 48 = @TIMEVALUE 1 V 20 59 = @IRR 3 M* 1a 79 = @TRIM 1 V 20 91 = @ISAAF 1 V 22 22 = @TRUE 0 1a 92 = @ISAPP 1 V 22 72 = @UPPER 1 V 20 20 = @ISERR 1 V 1a 41 = @VALUE 1 V 20 19 = @ISNA 1 V 1a 57 = @VAR 1 R 1a 38 = @ISNUMBER 1 V 20 55 = @VLOOKUP 4 M* 20 39 = @ISSTRING 1 V 20 32 = @YEAR 1 V 1a * M = Mixed, See specific format on next page. ** See note next page. 21 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought For the Mixed parameter functions on the previous page: @HLOOKUP @VLOOKUP require a cell, range, cell format. 9 elements. @CELL @IRR @NPV require a cell, followed by a range. 7 elements. @INDEX requires a range, cell, and cell. 9 elements. @COUNT is coded to expect a range only. Cell lists and the other optional entries allowed in Lotus are not supported in 123-Write. Note that those functions that work on strings should have a variable that points to a cell containing a string. If the variable portion of the function is not of the correct type, the cell to which the function is written will display "ERR" and that error can propagate through other dependent cells. For example, @@(cell) returns the value in the cell specified in "cell". So the cell referred to should have a string cell name, such as 'A6. The @CELLPOINTER("cell") and @CELL("cell") functions, expect the parameter to be something like: CONTENTS, PREFIX, FORMAT etc. So the cell that is supplied as a parameter should contain the string: 'CONTENTS or 'PREFIX or 'FORMAT etc. With Lotus release 2 and greater, it is possible to work with cells that contain strings. For example, if cell C23 contained "Hello World" then any cell that had the formula +C23 would also display "Hello World". Formulas like this in release 1A though, will not transfer the string, they expect a numeric argument, and will display 0 as the result. Any string cell in release 1A are considered to be equal to zero. Look at the @CHOOSE function in the demo for an example if you write a 1a compatible file. Don't forget that BASIC can and will move data around in memory. The segment and offset of a particular data item is not always static. Don't try to use variables to hold the segment and offset of a structure for a later call. I'm especially cautious on this point, and always embed the VARSEG and VARPTR functions into CALLs that use segment and offset addresses, as in the examples for this routine. ERRORS: No file open: No action taken. Oper% out of range (1-9): @ERR in current cell. (CP +) 22 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lCellMath COMPATIBILITY: All releases. CELL POINTER: Increased 1 column. SYNTAX: lCellMath(col1%, row1%, col2%, row2%, oper%) col1% is the column number of the 1st cell the math is to be performed on. row1% is the row number of the 1st cell the math is to be performed on. col2% is the column number of the 2nd cell the math is to be performed on. row2% is the row number of the 2nd cell the math is to be performed on. oper% is one of the following: 1 add cell 1 and cell 2. 2 subtract cell 2 from cell 1. 3 multiplication of cells 1 and 2. 4 Division of cell 1 by cell 2. 5 Cell 1 raised to the power of cell 2. lCellMath provides simple math operations on two cells. Any function numbers outside the range 1 through 5 will result in the target cell being tagged with @ERR. EXAMPLE: col1 = 0 'A row1 = 2 '3 col2 = 8 'I row2 = 5 '6 oper = 3 'Multiply CALL lCellMath(col1%, row1%, col2%, row2%, oper%) Will multiply A3 by I6 and place the formula in the current cell. ERRORS: No file open: No action taken. Oper% out of range (1-5): @ERR in current cell. (CP +) 23 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lClose COMPATIBILITY: All releases. CELL POINTER: N/A SYNTAX: lClose Closes the currently open spreadsheet file after writing the Lotus end of file marker and updating the worksheet range. The worksheet range is the cell that results from the {END}{HOME} key presses in Lotus. A general CLOSE statement in your program will NOT close the spreadsheet file. This differs from the 1.x versions of 123- Write which were written in BASIC. Note that all the routines in this library keep track of the spreadsheet file handle internally. lClose resets the handle to 0 and all the routines that write to the file naturally check for a valid handle before an attempt is made to write data to the file. Calling a routine when there is no file open, or the file wasn't opened via lFileOpen or lFileAppend, will not cause a crash because the internal handle is checked. However, if you fail to close the file with this routine, three things are possi- ble/probable: 1. Subsequent calls to lFileOpen or lFileAppend will fail because the internal handle still indicates a file open. 2. The disk's directory will not be updated properly. One file handle from DOS's pool will not be available to subsequent pro- grams until the machine is rebooted. 3. Lotus will beep and issue a "Part of file missing." error when the spreadsheet loads. Definitely not a reassuring happen- stance. Very little error checking is done in the 123-Write routines. It's your job to debug your mainline code so the file is properly opened and closed. When in the QB or QBX environment, lClose is called if there is a file open and your program terminates abnor- mally or you respond "OK" to one of QB's "You will have to re- start your program after this edit" prompts. EXAMPLE: CALL lClose ERRORS: No file open: No action taken. 24 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lCol COMPATIBILITY: All releases. CELL POINTER: Not changed. SYNTAX: lCol (column$) column$ is a one or two byte string. lCol is a FUNCTION, and as such must be declared in your program as an INTEGER function before any calls are made to it. Other- wise the compiler will treat it as an array reference. lCol returns the zero based column number for the corresponding column$, if column$ is a valid representation of a Lotus column. It must be between "A" and "IV". If the string is invalid, lCol will return a value of -1. The reason lCol is a FUNCTION rather than a sub program, is so that it can be used in calls that require an integer column number. You may notice I make liberal use of this function in the demo program. In reality however, I would use this function only where a real variable would come into use. The INPUT of a column letter in the demo is an example. Otherwise as I debug, I'd stick a stop statement after the function call, get it's result and use that value in the finished code for speed. EXAMPLE: DECLARE FUNCTION lCol%(a$) . . x = lCol ("AB") ' x should be equal to 27. (A-Z) = 26 ' + (A-B) = 2 ' - Zero Base = -1 ' ----- ' 27 See examples under lSetLoc and lSetCol also. NOTE: I keep a PRINT lCOL(" ") line in the immediate window while working in QB. That way, when coding, I can just hit F6, fill in the column, see the result and code the proper number into other call statements. ERRORS: No file open: OK, returns the column number. Out of range (A-IV): Returns -1 as column number. (CP 0) 25 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lColFunction COMPATIBILITY: All releases, except @ROWS, ver 2.0+ CELL POINTER: Increased one column. SYNTAX: lColFunction (startRow%, endRow%, oper%) startRow% represents the first row to operate on. endRow% represents the last row to operate on. oper% is set to one of the following: 1. @SUM the sum of the cells. 2. @AVG the average of the cells. 3. @COUNT the count of cells with valid data. 4. @MIN the lowest value in the range. 5. @MAX the highest value in the range. 6. @STD the stnd deviation of the range. 7. @VAR the variance of the range. 8. @ROWS the count of the rows, inclusive. lColFunction is a quick method of writing common columnar func- tions to a cell, or a series of cells. The routine assumes the column to operate on is the current column, so all that's needed in the call is the first row to include and the last row to include. You can set the cell pointer with lSetLoc if needed. An example is probably the best way to illustrate. Lets say you're writing a spreadsheet where the first three rows are titles and column headings etc. The first valid data row is 3, and you have 4 columns of data, 0 through 3 (A through D). The number of rows of data vary based on the database file you're reading, so you don't have a fixed ending row when the program is run. Code would look something like this: FOR i = 1 TO LOF(dataFile%) \ LEN(dataRecord%) ...writing a row loop... CALL lLineFeed 'Get set for next row. NEXT i 'after the last data item is written, your row pointer 'is one below the last row of data, and your column 'will be 0 (A) so: CALL lGetLoc(currentCol%, currentRow%) endRow% = currentRow% - 1 FOR i = 0 TO 3 CALL lColFunction(3, endRow%, 1) '@SUM NEXT ERRORS: No file open: No action taken. Oper% out of range (1-8): @ERR in current cell. (CP +) 26 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lConstMath COMPATIBILITY: All releases. CELL POINTER: Increased one column. SYNTAX: lConstMath(col%, row%, constVal#, oper%) col% is the column number of the cell the math is to be performed on. row% is the row number of the cell the math is to be performed on. constVal# is a double precision (8 byte real) value oper% is one of the following: 1 add cell and supplied constant. 2 subtract the constant from cell. 3 multiplication of cell by the constant. 4 division of cell by the constant. 5 Cell raised to the power of constant. lConstMath writes a simple math calculation using a cell address and a constant you supply. Note that the constant you pass must be an 8 byte double precision real value. Don't confuse the use of the term constant with BASIC's use (CONST), a constant in the Lotus sense is a hard number that won't change. See the example below for how the result would look in Lotus 1-2-3. If the operation code you supply is a negative number, then the order is reversed. If you called this routine with an operation code of -2 for example, the cell you specify would be subtracted from the constant, instead of the constant subtracted from the cell. Operation values outside the range of -1 to -5 and 1 through 5 will result in the target cell being marked @ERR. EXAMPLE: CALL lConstMath (0, 3, CDBL(5), 4) will divide the value of cell A2 by 5. The spreadsheet formula would appear as: +A2/5 CALL lConstMath (0, 3, CDBL(5), -4) will divide 5 by the value of cell A2. The spreadsheet formula would appear as: 5/A2 ERRORS: No file open: No action taken. Oper% out of range: @ERR in current cell. (CP +) 27 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lDate COMPATIBILITY: All releases. CELL POINTER: Increased one column. SYNTAX: lDate(dat$) dat$ is an 8 or 10 byte date string. Writes the Lotus date value of the date supplied in dat$ to the current cell. The value is adjusted to conform with the errone- ous values 1-2-3 uses. (Lotus thinks that there was a February 29th of 1900, which is incorrect, 1900 was not a leap year.) The date string passed should be between 01-01-1900 and 12-31-2099. Useful for doing those 30-60-90 day aging schedules everyone seems to need. Only two date formats will convert properly, an eight byte mm?dd?yy or a ten byte mm?dd?yyyy (Basic's) format. The only conversion that is done is to an 8 byte date is to prefix the year with a "19" or "20" depending on the date the program is run. If the date string passed to the routine cannot be convert- ed to a numeric value, it is written to the cell as a label instead of a number. If a default date format has not been set via a call to lSetDate- Form, the target cell is formatted to display the date in D1 format. (dd-mmm-yy) which requires a column width of roughly 10 characters. See the lSetCol & lSetCols routines for information on setting column widths. The lDate routine is in LDATE.BAS, and the actual assembly rou- tines are in two parts. lDate1 converts a string date to a long integer value, where 1 equals 01-01-1900. lDate2 does the actual write of the double precision value to the worksheet file. Yes, I couldn't figure out how to do the long to 8 byte real conver- sion in assembly. If you think you'd find use for the lDate1 sub program in other work, the object file name is LDATES.OBJ for regular strings and LDATESF.OBJ for far strings. They can be copied out of their respective library files with LIB.EXE. EXAMPLE: 'Date stamp the worksheet file: CALL lWriteStr("As of:", errCode%) CALL lDate(DATE$) ERRORS: No file open: lDate1: Returns date value. lDate2: No action taken. Date not 'convertible': lDate1: Returns zero. lDate2: Writes whatever long integer is passed to it with the current date format. 28 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lDown COMPATIBILITY: All releases. CELL POINTER: Increased count% rows. SYNTAX: lDown(count%) count% is the number of rows. lDown is the equivalent of hitting the down arrow key in Lotus. The next row to be written will be increased by the number of rows the routine is called with. If your target environment is Lotus 1-2-3 ver 1a, do not exceed 2047. The maximum row number is 8195. Attempts to 'cursor' down beyond row 8195 will leave the cursor in row 8195. EXAMPLE: move% = 5 CALL lDown(move%) 'The next cell to be written will be 5 below the 'current row, in the same column. ERRORS: No file Open: No action taken. Count% + current row > 8195: Cell pointer row set to 8195. (Ver 1a note! Maximum worksheet row count in 1a was 2047. lDown will not mind going beyond it. If you're writing 1a compatible spreadsheets, keep a seperate count to be sure you don't exceed 2048 rows.) 29 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lFileAppend COMPATIBILITY: All releases. CELL POINTER: Set to {END}{HOME} position of file. SYNTAX: lFileAppend(fileName$, ecode%) fileName$ is a fully qualified file name, that may include a drive and path if needed. ecode% is returned to indicate an error. -1 A spreadsheet file is already open. -2 Invalid filespec length. -3 DOS version < 2.0 (whew!) -4 The specified file is not a Lotus 123 version 1a, or 2.0+ file. Positive error codes indicate a DOS error, and are returned unchanged from DOS. An error code of 2 indicates "File Not Found". The error codes in DOS do not coincide with BASIC error code values however. See appendix A for some that I've come across. Use lFileAppend to open an existing spreadsheet file and add on to it. If your program is running under DOS 3.0 or higher, then the file is opened in "Deny All" mode. If a file extension is not specified (no . dot), then a .WKS extension is assumed. Lotus files need the {end}{home} cell position written early in the file, so 123-Write tracks this cell continuously. When opening a spreadsheet file to append data to it, this routine will read as much of the existing file to determine the current {end}{home} cell as needed, then gets ready to write any data you wish. The updated {end}{home} cell is written to the file when lClose is called. The cellpointer is set to the current {end}{home} cell, so the next cell to be written after lFileAppend is the {end}{home} cell. The {end}{home} cell will most likely have valid data in it, and the first thing you should do after a call to this rou- tine is position the cell pointer with a call to lSetLoc or lLineFeed before writing any data to the file. Now a caution, although a minor one. It is OK to open a file, position the cell pointer to a previously written cell and 'over-write' the data in that cell. The thing to keep in mind though, is that the cell is not physically over written in the disk file, but that another entry for that cell is added to the end of the file. This causes no problems with Lotus, but I'll throw in an example anyway. 30 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought lFileAppend (continued) Say you have a worksheet that has your Year to Date Widget sales in cell 'A5'. Your application can determine the year to date sales from anywhere of course, and you want it to open the spre- adsheet and update cell 'A5' whenever it is run. Let's say your application is run once a week. You open the spreadsheet for append with this routine, call lSetLoc to set the cell pointer to 'A5' and call lWriteNum to write the year to date widget sales figure. Then you close the file and go on your way. Now let's jump ahead to December when the New Year's Bonus sub- ject comes up, which is based on year to date widget sales. Amazingly, this is the only time the spreadsheet in question gets loaded. What will happen, is that Lotus will assign a value to cell 'A5' the first time it comes across it in the file, then change the 'A5' value the next time it reads it (your first update) and continue on, replacing the 'A5' value 52 times. Your disk file also has 52 entries in it for cell 'A5' which is a physical consideration. What you will end up with, is the last year to date widget sales figure in 'A5' which is fine. Now the reason I think it's not a major consideration is that when the spreadsheet is saved from within Lotus, only the current (last) value of 'A5' would be saved. The file size and loading time would revert back to normal. Few spreadsheets are ever only read into Lotus and not saved. On the other hand, if there were a large number of cells being over-written regularly, and the spreadsheet was only for some type of reference, things may get out of hand. A hint would be to write a macro in an out of the way place, named '\0' that would save the file as soon as it's loaded into 1-2-3. The lRange, cell pointer positioning rou- tines, lHideCol, and lWriteStr routines will allow you to write an invisible macro for just this purpose. ERRORS: Most (?) will be returned in the errCode% variable. 31 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lFileOpen COMPATIBILITY: All releases. CELL POINTER: Set to 0,0 (Cell A1). SYNTAX: lFileOpen(fileName$, errCode%) fileName$ is a fully qualified file name, that may include a drive and path if needed. errCode% is returned to indicate an error. -1 A spreadsheet file is already open. -2 File spec is too long or null. -3 The current DOS version is less than version 2.0. (Sad) Positive error codes indicate a DOS error, and are returned unchanged from DOS. The error codes in DOS do not coincide with BASIC error code values however. See appendix A for some that I've come across. lFileOpen opens a worksheet file for output. Pass the routine a valid file name which can include a drive and path if needed. If DOS version 3.0 or higher is in use, the file is opened with a "Deny All" access code. No error checking or parsing of the file name (except for an extension) is done. You're in charge here, and the error codes returned are few. If the file already ex- ists, it will be over written. You can pass any file name you wish. If the file name does not include an extension, ".WKS" is used by default. This routine writes the initialization data to the spreadsheet file. The initialization identifies the worksheet file as a version 1a file, and includes only the data that 1a would write. This does not seem to pose any problem with a ".WK1" extension. When Lotus 2.0 loads a ".WK1" file with a 1a signature, no errors are generated, so I assume no extension to file type check is done by Lotus 2.0 - 2.2. If there is already an open worksheet file, any calls to the lFileOpen routine will fail with an errCode of -1 being passed back. Use lClose to close the spreadsheet file, after which another call to lFileOpen can be made to open another spreadsheet file. EXAMPLE: CALL lFileOpen("D:\LOTUS\TESTFILE.WK1") will override the default "WKS" extension, and write the file to drive D, in the sub directory named LOTUS. ERRORS: Most (?) will be returned in the errCode% variable. 32 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lGetLoc COMPATIBILITY: All releases. CELL POINTER: Unchanged. SYNTAX: lGetLoc(whatCol%, whatRow%) whatCol% will be returned equal to the next column number that is to be written. whatRow% will be returned equal to the next row number to be written. Not a lot to say about this one, returns the row and column numbers of the next cell to be written. Just remember that the column and row coordinates are always 0 based, column A = 0 and row 1 = 0. See also the lSetLoc routine which allows you to set the row and column for the next write. EXAMPLE: CALL lGetLocation(currentCol%, currentRow%) IF (currentRow% + 1) MOD 20 = 0 THEN CALL pageHeader If you had a routine called pageHeader that you wanted to call for each 20 row "screen page" this would do it. (I think.) ERRORS: File not open: Variables returned unchanged, no action. 33 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lGlobal COMPATIBILITY: All releases. CELL POINTER: Unchanged. SYNTAX: lGlobal(item%, switch%) item% is a value from 1 to 5. switch% adjusts the specified item. When item% Worksheet global switch% is: item effected: value: 1 Calculation iterations. 1 through 255 1 * 2 Auto recalculation. 0 = Off (Manual) otherwise On (Automatic) * 3 Calculation order. 0 = natural * 1 = column-wise 2 = row-wise 4 Unformatted Printing. 0 = Formatted print * otherwise Unformatted print 5 Global worksheet protection 0 = Unprotected * otherwise protection On This routine will allow you to change any of the default global settings of the worksheet. If the file is not opened via lFi- leAppend, the defaults for each item are marked with the * aster- isks above. The qualifier about how the file is opened is added because these settings could already be set in the file that is being appended to. To speed processing, the settings above are not read when lFileAppend is used, however, they can be added to a file opened in append mode. The last global switch read will take precedence. A note is in order regarding protection. As a default, 123- Write will write each cell as a protected cell, but the protec- tion won't be enabled unless it is specifically turned on. In Lotus the /Worksheet Global Protection Enable series of commands will do it, or in 123-Write a call to this routine. Cell protec- tion can be disabled for a series of cells by using the lSet- CellForm routine, which will toggle numeric and string cell protection off and on. See also lSetDateForm and lSetTimeForm which have toggles for cell protection also. ERRORS: File not open: No action taken. Invalid item%: No action taken. (CP 0) 34 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME lHideCol COMPATIBILITY: Release 2.0+ CELL POINTER: No change. SYNTAX: lHideCol(col%, writeFlag%) col% is the zero based column number. writeflag% indicates that the hidden column record should be written to the spreadsheet file. lHidecol can be used to mark any column in the worksheet as a hidden column. Multiple columns can be hidden with repeated calls to this routine. Lotus 1-2-3 uses a single record for all 255 columns in the spreadsheet, and 123-Write maintains an inter- nal record of the columns you mark as hidden. When this routine is called with the write flag non-zero, the hidden column record is written to the file. A check is made of the column number first. If the column number is less than zero, a check is made of the write flag. If the write flag is non zero, the hidden column record is written to the file. This allows you to specify writing the record without specifying a column to hide (ie: call with -1 and -1). The 123-Write internal record is not reset until a call is made to lFileOpen or lFileAppend. This means that if your code is hiding columns interactively with the user, you could call this routine with the write flag set each time the user selected a column. The end result would be that all selected columns are hidden, but the disk file would be needlessly bloated. A better method in this situation would be to leave the write flag zero as columns are marked, then call this routine with the column varia- ble set to less than zero and the write flag non zero immediately before calling lClose. Because 123-Write doesn't allow nested functions and formulas, the ability to hide a column is very handy. You could place the needed comparison data and constant data into cells that are hidden, and preserve an uncluttered appearance in the resulting spreadsheet. ERRORS: No file open: No action taken. Column <0 or >255: Write flag checked. If flag is non-zero, the record is written. 35 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lLeft COMPATIBILITY: All releases. CELL POINTER: Column is decreased count% times. SYNTAX: lLeft(count%) count% is the number of columns. lLeft is the equivalent of hitting the left arrow key in Lotus. The next col to be written will be decreased by the number of columns the routine is called with. Attempts to cursor left beyond column 0 will leave the cursor in column 0 (A). EXAMPLE: move% = 5 CALL lLeft(move%) 'The next cell to be written will be 5 to the 'left of the current cell, in the same row. ERRORS: No file open: No action taken. current column - count% < 0: Column set to zero. 36 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lLineFeed COMPATIBILITY: All Releases. CELL POINTER: Col set to zero, Row incremented by 1. SYNTAX: lLineFeed When called, resets the internal column pointer to 0, (Column A) and increases the row pointer by one. The next cell to be writ- ten will one row lower in column A. If an attempt is made to write past row 8195, the cell pointer will stay in row 8195. See lColFunction for an example of using lLineFeed. ERRORS: No file open: No action taken. Current row=8191: Row remains at 8191. Column set to zero. 37 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lLink COMPATIBILITY: Release 2.2+ CELL POINTER: Column increamented by one. SYNTAX: lLink(fileSpecRange$) fileSpecRange$ is a properly formatted linked cell reference. It must be in the: <<fileSpec>>RangeName format. lLink is intended to be used only with spreadsheet files that are targeted to be used by Lotus 1-2-3 Release 2.2 or 3.0. Spread- sheet linking was not available in earlier releases. To keep the assembly language code as small and tight as possi- ble, I did not add strong type checking to the string you pass. Therefore, the string passed to this routine must be exactly as it would be typed into a cell in 1-2-3. Two left angle brackets, the full drive and path (if needed) the spreadsheet file name, two right angle brackets and the cell (not recommended) or range name to link in. Care should be taken to use a range name in the spreadsheet that contains the data to be read into the current worksheet. If a cell reference is made to a linked worksheet, and that worksheet is modified (a row inserted for example) the data read into the current worksheet will be erroneous. By using range names, no matter how the referenced worksheet is changed, the result will be correct. I know, the range could be deleted from the refer- enced worksheet, but that's life. The range name to reference should be early in the file if it's a file written by 123-Write. If the range is buried in the file, intstead of being written right after the lFileOpen call, the link may fail. Single cell ranges seem to work more reliably. Remember that 123-Write does not calculate any formulas or func- tions. When 123-Write writes a cell to a spreadsheet file, if it's a formula, calculation or function, the result is written as zero. Lotus calculates all formulas and functions when it reads in the worksheet, so I elected to take the quick way out. Why calculate something twice? Especially something like a four quadrant arctangent! Therefore if you want to link in a cell from a spreadsheet that was written by 123-Write, the result may be a zero. The best way around this is to have a test cell in an out of the way place, that does a comparison for zero on some part of the linked spreadsheet. See the next page for an exam- ple. 38 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought lLink (continued) For example if you use the Lotus @TRUE function in a hidden cell of a reference sheet written by 123-Write, and name that cell TRUE_RANGE, then in the current worksheet include a cell that links to it, let's say in cell AA1, like: AA1: <<D:\123\123WRITE.WK1>>TRUE_RANGE Then in AA2, place the link to the data you need for the current worksheet. Let's assume it's in 123WRITE.WK1 in a cell range named "Result" AA2: <<D:\123\123WRITE.WK1>>RESULT Then you could place the function @NA (the value NA, in Lotus- Speak) in cell AA3: AA3: @NA So now we have everything pretty well set. Now, the value of the cell: RESULT in: 123WRITE.WK1 is needed in the "main" worksheet at cell C3. Using the lCellFunction routine to write an @IF formula, you could write: C3: @IF(AA1,AA2,AA3) The result of all this is, is a flag to tell you if the target worksheet file has been loaded into and saved from Lotus. If 123-Write had written 123WRITE.WK1 then the result of the @TRUE function in TRUE_RANGE would be zero, and the current worksheet would compare that zero via cell AA1 and use the value NA in cell C3. If Lotus had loaded the 123WRITE.WK1 file, and it had been saved, @TRUE in TRUE_RANGE would be 1, cell AA1 in the current worksheet would be 1, and cell C3 would equal the value read into AA2, which is the calculated result in the RESULT range. Clear as mud right? It sounds worse than it is, in reality it is really simple to implement from 123-Write or Lotus. ERRORS: File not open: No action taken. Null String: No action taken. (CP 0) String > 240 bytes: No action taken. (CP 0) 39 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lMultiCellMath COMPATIBILITY: All Releases. CELL POINTER: Column increamented by one. SYNTAX: lMultiCellMath(dSeg%, dOfs%) dSeg% is the VARSEG() of a single dimension integer array with the column and row coordinates and the operation to be performed. dOfs% is the VARPTR() to the array. This routine is going to take some getting used to. It's purpose is to write calculations to the current cell using multiple cell references. (There is a limit to the number of referenced cells, roughly 341(?) cells but I wouldn't want to try and find the maximum.) The integers in the array are read and acted upon sequentially. Each cell operation requires three elements, the column number, the row number and what to do with it, the operation code. The routine starts reading from the specified element in the array, so be sure to put the first column reference in the element you use as an argument to the VARSEG and VARPTR functions. The end of the formula is indicated by an operation code of zero after the last cell coordinates. DO NOT assume that the upper bound of the array is checked, it is NOT. If the last operation code in the array is not a zero, this routine will continue reading forever, if it doesn't happen to hit a zero in the third "set". The operation codes, the third element of each set, are: 0. end of calculation. 1. add next cell. 2. subtract next cell. 3. multiply by the next cell. 4. divide by the next cell. Again, an example might be the best way to illustrate. A work- sheet with 4 columns (0-3, A-D) of data. We want to take column A's (0) value, add column B's (1) value to it, and subtract the value in column D (3), placing the result in column E (4) for each row. See the next page for the code example. ERRORS: File not open: No action. (CP 0) Actual 3rd integer not (1-5): La La land. (CP ???) A element, multiple of 3 not in range (0-5): Terminates function. (CP +) 40 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought LMultiCellMath (continued) Example 1. DIM Array%(1 TO 9) . worksheet file is opened etc... . startRow% = 3 'The operations and columns endRow% = 30 'are constant, so set them: Array%(1) = 0 'Element '1' col number (A) Array%(3) = 1 'Element '3' operation: Add next Array%(4) = 1 'Element '1' col number (B) Array%(6) = 2 'Element '3' operation: Sub next Array%(7) = 3 'Element '1' col number (D) Array%(9) = 0 'Element '3' operation: End calc FOR i% = startRow% TO endRow% . . write the row data, columns 0 to 3 (A - D) . . Array%(2) = i% 'The row numbers are going Array%(5) = i% 'to change to reflect the Array%(8) = i% 'row we're in. ' 'Then write the formula to column E: ' CALL lMultiCellMath(VARSEG(Array%(1)),VARPTR(Array%(1)) CALL lLineFeed NEXT i% Again note that BASIC can move data around in memory, and it is important not to assume that an array's (or structure's) address in memory will stay the same. My examples show the VARSEG() and VARPTR() functions embedded in the calls because I don't like to take any chances. Just a chicken at heart I guess. Also note that a structure could be used instead of an array, just be sure the "third element = 0" rule is observed. See the next page for a coding example that duplicates the above, but is a little easier to comprehend. 41 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought LMultiCellMath (continued) Example 2. TYPE MultiMathStructure col AS INTEGER row AS INTEGER opr AS INTEGER END TYPE DIM multiMath(1 to 3) AS MultiMathStructure . worksheet file is opened etc... . startRow% = 3 'The operations and columns endRow% = 30 'are constant, so set them: multiMath(1).col = 0 'Cell '1' col number (A) multiMath(1).opr = 1 'Cell '1' operation: Add next multiMath(2).col = 1 'Cell '2' col number (B) multiMath(2).opr = 2 'Cell '2' operation: Sub next multiMath(3).col = 3 'Cell '3' col number (D) multiMath(3).opr = 0 'Cell '3' operation: End calc FOR i% = startRow% TO endRow% . . write the row data, columns 0 to 3 (A - D) . . multiMath(1).row = i% 'The row numbers are going multiMath(2).row = i% 'to change to reflect the multiMath(3).row = i% 'row we're in. ' 'Then write the formula to column E: ' CALL lMultiCellMath(VARSEG(multiMath(1)),VARPTR(multiMath(1)) CALL lLineFeed NEXT i% 42 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lPrintBorders COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lPrintBorders(rulc%,rulr%,rlrc%,rlrr%,culc%,culr%,clrc%,clrr%) rulc% is the Border Row upper left column rulr% is the Border Row upper left row rlrc% is the Border Row lower right column rlrr% is the Border Row lower right row culc% is the Border Column upper left column culr% is the Border Column upper left row clrc% is the Border Column lower right column clrr% is the Border Column lower right row Yes, eight parameters for one call. Lotus 1-2-3 requires all eight in one record, consequently it has to be written to the file as one record. Lotus uses a unique method to indicate an undefined range which is also used internally by 123-Write. It would be cumbersome to have to call this routine with the proper values, so I wrote a test into the code for this function: If the sum of all the range values (either the column range or the row range) are less than zero, the range is not defined. When you define a column or row print border in Lotus, it is acceptable to define one cell as the 'column' or one cell as the 'row', you can do that in 123-Write also. For instance, to define column A as the column border and row 1 as the row border, just call lPrintBorders with eight zeros. EXAMPLES: 'Set only border rows of rows 1 through 4: CALL lPrintBorders (0, 0, 0, 3, -1, 0, 0, 0) 'The column range above is less than 0: not defined. 'Set only border columns of columns A and B: CALL lPrintBorders (-1, -1, -1, -1, 0, 0, 1, 0) 'The row range above is less than 0: not defined. 'Set both the above as the borders: CALL lPrintBorders (0, 0, 0, 3, 0, 0, 1, 0) 'the border rows are 1 through 4 and columns are A & B. ERRORS: File not open: No action taken. Illegal column or Illegal row number: Written to the file. No check is made. 43 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lPrintMargins COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lPrintMargins(lm%, rm%, tm%, bm%, pageLen%) lm% is the left margin rm% is the right margin tm% is the top margin bm% is the bottom margin pageLen% is the page length lPrintMargins sets the printer margins used by Lotus for subse- quent printing. When 123-Write opens (rather than appends to) a worksheet file, these variables are set to the default values of: Left = 4 Right = 76 Top = 2 Bottom = 2 Page = 66 If needed, the default, or previously set (if the file was opened for append) margins can be changed by calling this routine. NOTE that all the variables will be written to the worksheet file. If you want to change just the right margin for example, you must still supply all the other parameters. Lotus requires all mar- gins be set in one record. EXAMPLE: lm% = 10 rm% = 130 tm% = 2 bm% = 2 page% = 66 CALL lPrintMargins(lm%, rm%, tm%, bm%, page%) 'In effect just changes the left and right print margins. ERRORS: No file open: No action taken. Illegal column or Illegal row number: Written to the file. No check is made. 44 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lPrintRange COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lPrintRange (ulc%, ulr%, lrc%, lrr%) ulc% is the upper left column of the range. ulr% is the upper left row of the range. lrc% is the lower right column of the range. lrr% is the lower right row of the range. lPrintRange sets up the default print range for the worksheet. This allows you to predetermine the range, and then you can just add a macro to print it when Alt-P is pressed. Saves time in macro writing if the range is preset, by eliminating all the range input needed to set up the print range. The example below shows how it can be done. If you use the lGetLoc routine and save the bottom-most row and right-most col to variables, then set the print range, it allows you to always define the full range of cells for printing regardless of the size of the work- sheet. EXAMPLE: 'Let's assume all the data has been written 'to the spreadsheet, and that the upper left 'corner of the print range is 0, 0 (A1) and the 'lower right corner is in the variables lrc% and 'lrr% CALL lPrintRange (0, 0, lrc%, lrr%) column% = lCol ("Z") CALL lSetLoc (column%, 0) 'Macro in Z1 CALL lRange("\P", column%, 0, column%, 0) 'Name it CALL lWriteStr("/PPAGQ", errCode%) '1 cell macro 'The Alt-P macro is just a PRINT, PRINTER, ALIGN, ' GO, QUIT sequence. ERRORS: No file open: No action taken. Illegal column or Illegal row number: Written to the file. No check is made. 45 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lPrintSetup COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lPrintSetup (setup$) setup$ is the Lotus style print control string. Assigns a printer setup string to the worksheet. The string you pass to this routine is used exactly as received. An exception is that only the first 40 characters are used if the string is over 40 characters long. Remember to use the back-slash (\) character to start and three digit ASCII numbers to define the setup string. EXAMPLE: 'Setting my printer into near letter quality mode 'requires an ESC-W-1 string. ASCII 27, 88, 49 setup$ = "\027\088\049" CALL lPrintSetup (setup$) ERRORS: No file open: No action taken. Null String: No action taken. (CP 0) String > 39 bytes. Truncated to 39 bytes. (CP +) 46 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lPrintTitle COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lPrintTitle (title$, header%) title$ is the string to assign to the print output. header% is a flag to indicate a title (non-zero) or a footer (zero) When called, sets up either the print title, if header% is non- zero, or the print footer if header% is zero. The string passed to this routine is truncated if over 240 characters. The special formatting characters should be included in the string passed to this routine. The | character can be used to break the line into three separate text blocks, each aligned on the page differently. The @ character will be replaced by the current system time when the print command is issued. The third format character is # which indicates page numbering. The page number is only reset to 1 when the /Print command is invoked. EXAMPLE: header$ = "|Income Statement" footer$ = "|2nd Quarter 1989" CALL lPrintTitle (header$, -1) CALL lPrintTitle (footer$, 0) 'Both the header and footer above will be centered, 'There is no 'Left' block. header$="Income Statement||Page #" 'Will result in a left aligned title, with the page 'number right aligned for instance. ERRORS: No file open: No action taken. Null String: No action taken. (CP 0) String > 240 bytes. Truncated to 240 bytes. (CP +) 47 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lRange COMPATIBILITY: All Releases. CELL POINTER: No change. SYNTAX: lRange(rangeName$, ulc%, ulr%, lrc%, lrr%) rangeName$ is the name to assign. ulc% is the upper left column of the range. ulr% is the upper left row of the range. lrc% is the lower right column of the range. lrr% is the lower right row of the range. lRange assigns a name of up to 15 characters to the specified range. Any characters over 15 are ignored. The range name supplied is NOT parsed to replace any invalid characters. The name passed to the routine remains unaltered. Range names are a great time saver in Lotus. When writing a spreadsheet via 123-Write, you can't be sure that a user won't move data around after you've written the file. This is a prob- lem when writing macros especially, if you "hard code" a cell reference in a text string, it will always refer to that cell, no matter where the actual data you want to work with is. By using macros that have range names in them exclusively, the end user can move everything around, yet your macros will still work correctly. As mentioned at the start of this document, if possible, the range names should be written early in the file. EXAMPLE: CALL lRange("Income", lCol("B"), 0, lCol("D"), 19) Assigns the name INCOME to the range from B1 through D20. ERRORS: No file open: No action taken. Null String: No action taken. (CP 0) String > 15 bytes. Truncated to 15 bytes. (CP +) 48 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lRight COMPATIBILITY: All Releases. CELL POINTER: Column incremented by count%. No change to row. SYNTAX: lRight(count%) count% is the number of columns. lRight is the equivalent of hitting the right arrow key in Lotus. The next column to be written will be increased by the number of columns the routine is called with. The maximum column number is 255 (IV). Attempts to 'cursor' right beyond column 255 will leave the cursor in column 255. EXAMPLE: move% = 5 CALL lRight(move%) 'The next cell to be written will be 5 columns 'to the right of the current column, in the 'same row. ERRORS: File not open: No action taken. Current column + count > 255: Column set to 255 (IV). 49 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lRowFunction COMPATIBILITY: All releases, except @COLS, ver 2.0+ CELL POINTER: Increased one column. SYNTAX: lRowFunction(startCol%, endCol%, oper%) startCol% represents the first col to operate on. endCol% represents the last col to operate on. oper% is set to one of the following: 1. @SUM the sum of the cells. 2. @AVG the average of the cells. 3. @COUNT the count of cells with valid data. 4. @MIN the lowest value in the range. 5. @MAX the highest value in the range. 6. @STD the stnd deviation of the range. 7. @VAR the variance of the range. 8. @COLS the count of the cols inclusive. lRowFunction is a quick method of writing common row functions to a cell, or a series of cells. The routine assumes the row to operate on is the current row, so all that's needed in the call is the first column to include and the last column to include. To illustrate, lets say you're writing a spreadsheet where you want the sum (@SUM) of the first four columns (A-D) in column E of each row: FOR i% = 1 TO LOF(dataFile%) \ LEN(dataRecord%) . ...writing a row loop... . CALL lRowFunction(0, 3, 1) '@SUM the row. CALL lLineFeed 'Get set for next row. NEXT i% ERRORS: File not open: No action taken. oper% <1 or >8: @ERR written to current cell. (CP +) 50 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetCellForm COMPATIBILITY: All releases, except as noted below. CELL POINTER: No change. SYNTAX: lSetCellForm(formatCode%, decimals%) formatCode% Indicates the desired format for numeric and string cells written after this routine is called. decimals% Usually the number of decimal places you want displayed in subsequent cells. FormatCode% decimals% ----------- --------- 1 Fixed number of decimal places. 1 thru 15 2 Scientific notation. 1 thru 15 3 Currency format. 1 thru 15 4 Percent format. 1 thru 15 5 Comma format. 1 thru 15 6 Special format (see below) 7 Set cell protection. 0=Off else On Sets the default cell display for subsequent numeric cells. Default string alignment can be changed, see lSetStrForm. For the Fixed, Scientific, Currency, Percent and comma formats, the decimals% parameter represents the number of decimal places to display, from 0 through 15. For the protection option, the decimals% is used as a flag. If it is zero, cell protection will be turned off. Any non-zero value will turn on cell protection. Note that if you write unprotected cells, 1-2-3 will highlight them when the spreadsheet is loaded. For the Special option, number 6, the following formats are set, based upon the value of decimals%: value Result: value result: ----- ------- ----- ------- 0 + / - format. 9 Date, Int'l 1 (2.0+) 1 General 10 Date, Int'l 2 (2.0+) 2 Date 1: dd-mmm-yyyy 11 Time, Int'l 1 (2.0+) 3 Date 2: dd-mmm 12 Time, Int'l 2 (2.0+) 4 Date 3: mmm-yy 13--15 Default (Cell takes 5 Text whatever format is set 6 Hidden (2.0+) by /Worksheet Global 7 Time 1: hh:mm:ss AM/PM (2.0+) Format command. 8 Time 2: hh:mm AM/PM (2.0+) 51 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought lSetCellForm (continued) Individual cells, though marked as protected cells aren't "write protected" unless worksheet protection is enabled. Default worksheet protection is disabled, so a call to lGlobal is needed to "write protect" protected cells. When a worksheet file is first opened, if no other formats have been set (from a previously completed file, for example) the format string is set to: Protected, Special, Default by 123- Write. EXAMPLES: CALL lSetCellForm(1,3) 'result: ####.### CALL lSetCellForm(4,2) 'result: #,###.## CALL lSetCellForm(3,2) 'result: $#,###.## CALL lSetCellForm(7,-1) 'results in cell protection being turned on. ERRORS: File not open: Cell format set. (CP 0) formatCode% <1 or >7: No action taken. Last (or default) is still in effect. (CP 0) 52 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetCol COMPATIBILITY: All releases. CELL POINTER: No change. SYNTAX: lSetCol(col%, colWidth%) col% is the column number to set, colWidth% is the width for the column. Sets the column width for an individual column. See lSetCols if you want to set a range of columns at one time. The Lotus default column width is 9 characters wide. You can set the width of any column to another value by calling this routine. The valid widths are 1 to 240 maximum. EXAMPLE: CALL lSetCol(3, 20) CALL lSetCol(lCol("D"),20) both set column D's width to 20 characters. ERRORS: File not open: No action taken. column%<0 or >255: Set to low byte value. (CP 0) colWidth% <0 or >240: Set to low byte value. (CP 0) 53 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetCols COMPATIBILITY: All releases. CELL POINTER: No change. SYNTAX: lSetCols(colWidths%()) colWidths%() is a single dimension integer array, the element number indicates the column, the content of the element indicates the width for the column. lSetCols is for setting multiple columns to different widths with one call. Whether you use this routine or set the column widths individually with lSetCol is your call. lSetCols is written in BASIC, the source file being LSETCOLS.BAS. lSetCols calls lSet- Col using the LBOUND and UBOUND of the passed array as a loop control, so the columns have to be within one range. The integer array holds the width for each column. The position in the array determines the column. If you want to use this routine to set column zero, there must be a zero element in the array passed. No OPTION BASE 1 statement in this case, or use a DIM array%(0 to whatever%) statement to over-ride the OPTION BASE statement. With QuickBASIC 4.0+ and BASCOM 6.0+ it is possible to set lower array bounds of other then 0 or 1, which is handy with this routine. If you want to set columns 9 through 17 for example, you can: REDIM temp%(9 TO 17) ...set elements 9 through 17 to desired widths... CALL lSetCols(temp%()) EXAMPLE: See above. ERRORS: A lower bound less then 0 will result in NO column width data at all being written. (CP 0) If the width for a column (element) is less than 1 or greater than 240 that column is skipped, and it's width is left at the default (or other) setting of 9. (CP 0) File not open: No action. 54 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetDateForm COMPATIBILITY: See below. CELL POINTER: No change. SYNTAX: lSetDateForm (format%, protect%) format% is the desired format number: 1 dd-mmm-yy 16-Feb-53 2 dd-mmm 16-Feb 3 mmm-yy Feb-53 (1-2-3 ver 2.0+ only) 4 mm-dd-yy 02-16-53 (1-2-3 ver 2.0+ only) 5 mm-dd 02-16 protect% is a flag to turn date cell protection on (non-zero) or off (zero). Setting the date format is optional, it will default to format 1, the dd-mmm-yy arrangement when the worksheet is opened with lFileOpen. The date format you set here will be used with subse- quent calls to the lDate and lToday routines, numeric and string cells are left unchanged. Note that this is a change from version 1.0, where the date format on calls to lDate was always format 1. All five formats are available in Lotus 1-2-3 version 2.0 and greater. Only formats 1, 2 and 3 are recognized by version 1a of Lotus. A date format can also be specified by calling lSetCellForm as in version 1.0. However, lSetForm causes all numeric cells written after it is called to be displayed with the specified format. This routine allows you to cut your code down by setting both a date format and a standard cell format and not being forced into the default dd-mmm-yy setting. EXAMPLE: 'Assume you're reading and writing 10 field records where 'the 5th and 7th fields are dates (all others numeric): CALL lSetDateForm(4,1) 'Lotus D4, protected cell FOR i = 1 TO 10 INPUT #inputFile, rec$ IF i = 5 OR i = 7 THEN CALL lDate(rec$) ELSE a# = VAL(rec$) CALL lWriteNum(a#) END IF NEXT ERRORS: File not open: Date format selection is set. (CP 0) format%<1 or >5: No action taken. Last (or default) remains in effect. (CP 0) 55 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetLoc COMPATIBILITY: All releases. CELL POINTER: Set to parameters. SYNTAX: lSetLoc(newCol%, newRow%) newCol% is the next column you want written, newRow% is the next row to write. Use lSetLoc to position the internal column and row pointers to any cell (within 255 columns and 2048 or 8192 rows) that you want to write to next. Column and row ranges are checked, use a maxi- mum row of 2047 for 1-2-3 release 1a, 8191 for later versions. Only thing to mention (again?) is that the column and row coordi- nates are 0 based, but at least with the lCol function, we can get a little help: Example: CALL lSetLoc(lCol("C"), 6) Will result in the cell pointer being set to write to 'C7' next. ERRORS: File not open: No action taken. Col<0 or >255: No action taken. (CP 0) Row<0 or >8191: No action taken. (CP 0) 56 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetStrForm COMPATIBILITY: All releases except number 5, 2.0+ only. CELL POINTER: Not changed. SYNTAX: lSetStrForm(formatCode%) formatCode% is any one of the following: 1 Left justify text. ' (Default) 2 Center text in the cell: ^ 3 Right justify text. " 4 Repeating character: \ 5 The non-print character: | (2.0+ only) lSetStrForm sets the DEFAULT label alignment character for all strings written to the file (after this routine is called), if a label prefix character is not the first character in the string. See the lWriteStr routine for more information on writing string data. If the format code you pass to this routine is not one of the five available, the last label prefix character selected, or the default single quote (') is selected. The single quote (left justify) is the default label prefix character selected when the spreadsheet file is opened via lFile- Open or lFileAppend, unless a previous label prefix character has been defined during the same session. The cell protection can be set to on or off by the lCellForm rou- tine. ERRORS: File not open: String cell format byte set as above. formatCode<1 or >5: No action, last seeting stays. (CP 0) 57 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSetTimeForm COMPATIBILITY: Release 2.0+ only. CELL POINTER: Not changed. SYNTAX: lSetTimeForm (format%, protect%) format% is the desired format number: (am/pm indicator) 1 hh:mm:ss(am/pm) 09:33:45pm (am/pm indicator) 2 hh:mm(am/pm) 09:33pm (24 hour clock) 3 hh:mm:ss 21:33:45 (24 hour clock) 4 hh:mm 21:33 protect% is a flag to toggle cell protection on (non-zero) or off (zero). Setting the time format is optional, it will default to format 1, the hh:mm:ss(am/pm) arrangement when the worksheet is opened. The time format you set here will be used with subsequent calls to lTime. Note that time formats (and functions) were not in- cluded with Lotus until release 2.0. EXAMPLE: An example here would be the same as given with the lSetDateForm routine, and since they're both so similar, I'll refer you to that routine for an example. ERRORS: No file open: Time format set. (CP 0) format<1 or >4: No action taken, last setting stays.(CP 0) 58 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSortKey COMPATIBILITY: All releases. CELL POINTER: Not changed. SYNTAX: lSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascnd%) primry% indicates whether to set the primary sort key (non-zero) or the secondary sort key (zero). ulc% is the upper left column of the key range. ulr% is the upper left row of the key range. lrc% is the lower right column of the key range. lrr% is the lower right row of the key range. ascnd% sets the sort order, ascending (non-zero), or descending (zero). If a sort range has been set, the primary sort key and the sec- ondary sort key can be set via this routine. As in lPrintRange, this routine can alleviate the need for long macros. If the range of data to manipulate is known, it can all be set up via 123-Write, and a much simpler macro can be written, one that doesn't need to determine boundaries etc. Note that the key ranges must be within the data sort range. EXAMPLE: CALL lSortKey (-1, 0, 0, 0, endRow%, -1) 'Sets the primary sort key to cell A1 to cell Ax 'where x is a variable row number. ERRORS: File not open: No action taken (CP 0) Invalid cells: Blissfully written to file. (CP 0) 59 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lSortRange COMPATIBILITY: All releases. CELL POINTER: Not changed. SYNTAX: lSortRange(ulc%, ulr%, lrc%, lrr%) ulc% is the upper left column of the sort range. ulr% is the upper left row of the sort range. lrc% is the lower right column of the sort range. lrr% is the lower right row of the sort range. lSortRange sets up the default sort range for the worksheet. This allows you to predetermine the range, and then you can just add a macro to print it when Alt-S is pressed. Saves time in macro writing if the range is preset, by eliminating all the range input needed to set up the print range. The example below shows how it can be done. If you use the lGetLoc routine and save the bottom-most row and right-most column to variables, then set the sort range, it allows you to always define the full range of cells for sorting regardless of the size of the work- sheet. EXAMPLE: 'Let's assume all the data has been written 'to the spreadsheet, and that the upper left 'corner of the sort range is 0, 0 (A1) and the 'lower right corner is in the variables lrc% and 'lrr% CALL lSortRange (0, 0, lrc%, lrr%) column% = lCol ("AA") CALL lSetLoc (column%, 0) 'Macro in AA1 CALL lRange("\S", column%, 0, column%, 0) 'Name it CALL lWriteStr("/DSGQ") '1 cell macro 'The Alt-S macro is just a DATA, SORT, GO, QUIT sequence. ERRORS: File not open: No action taken. Invalid cells: Happily written to the file. 60 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lTime COMPATIBILITY: Release 2.0+ only. CELL POINTER: Advanced to next column. SYNTAX: lTime(timeStr$) timeStr$ is a valid string representation of a legitimate time. (What?) The expected format is: hh:mm:ss or hh:mm Don't really know how much use there may be for this routine, but since I included the numeric date handling capability, I figured I should also include time handling. No extended checking is done with the string you pass to this routine. If it isn't eight characters (hh:mm:ss) or five charac- ters (hh:mm) it writes the string to the file instead of the numeric value of the time. The routine is coded to use the format set with lSetTimeForm, and if there isn't a format set, it uses time format 1, Lotus version 2.0+ will display it in: hh:mm:ss AM or hh:mm:ss PM The cell contents are a fraction, indicating the fraction of a day that has passed. The lTime sub-program, written in BASIC is contained in the LTIME.BAS file. The assembly language routine lTime1 returns the number of seconds since midnight for a given time. (ala BASIC's TIMER function.) Lotus interprets time values based on the percentage of a day. For example .25 is 6:00 AM, .50 is 12:00 Noon. The lTime sub divides the seconds since midnight by the number of seconds in a day, and lTime2 writes the result to the spreadsheet file in the currently set time format. >> NOTE << Time values were not introduced in Lotus until release 2.0. You may use this routine to write a worksheet that will be read by a ver 1a copy of Lotus however. The values will be correct, but it is impossible to display them in "time format" with version 1a. The cell format indicator in 1a will display (?) instead of (D6) as it does in 2.0+. EXAMPLE: CALL lTime$(TIME$) 'would write the system time to the current spreadsheet cell. ERRORS: File not open: lTime1 will return a long int (TIMER) (CP 0) lTime2 will take no action. (CP 0) Invalid time$: String passed is written to the file. (CP +) 61 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lTimeNow COMPATIBILITY: Release 2.0+ only. CELL POINTER: Advanced to next column. SYNTAX: lTimeNow lTimeNow will write the Lotus 1-2-3 version 2+ formula @NOW in the current cell. In addition, the cell is formatted to display in the currently set time format. Use lToday to write the @NOW formula with a date display. EXAMPLE: CALL lWriteStr("The Current date is:", errCode) CALL lRight(2) 'adjust for long label cell CALL lToday CALL lWriteStr("The Current time is:", errCode) CALL lRight(2) 'adjust for long label cell CALL lTimeNow 'will display fresh time each re-calc. ERRORS: No file open: No action taken. 62 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lToday COMPATIBILITY: All Releases. CELL POINTER: Advanced to next column. SYNTAX: lToday Writes the Lotus function @TODAY/@NOW to the current cell. The cell is formatted for dd-mmm-yy display (D1) if there is no default date format set. The system date will be displayed in the target cell when the worksheet is loaded into Lotus. When loaded by 1-2-3 release 2.0+, the @TODAY function is con- verted to @NOW automatically. See lTimeNow for an example. ERRORS: No file open: No action taken. 63 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lUp COMPATIBILITY: All Releases. CELL POINTER: Advanced to next column. SYNTAX: lUp(count%) count% is the number of rows. lUp is the equivalent of hitting the up arrow key in Lotus. The next row to be written will be decreased by the number of rows the routine is called with. Attempts to 'cursor' above beyond row 0 will leave the cursor in row 0. EXAMPLE: move% = 5 CALL lUp(move%) 'The next cell to be written will be 5 rows 'above the current row, in the same column. ERRORS: No file open: No action taken. Current column - count% < 0: Column set to 0. 64 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lWriteErr COMPATIBILITY: All Releases. CELL POINTER: Advanced to next column. SYNTAX: lWriteErr This routine is used internally to write the function @ERR to the current cell when you do something out of line, like pass parame- ters that are outside of the allowed range. Seems only fair that you should be able to call it when an end user of your program does something out of line. I know, end users always respond to input prompts in only the ways we programmers expect, but it's here just in case. ERRORS: No file open: No action taken. 65 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lWriteInt NAME: lWriteNum COMPATIBILITY: All Releases. CELL POINTER: Advanced to next column. SYNTAX: lWriteInt(integerVal%, errCode%) SYNTAX: lWriteNum(doubleVal#, errCode%) OK, so the meat and potatoes come near the end of the documenta- tion. It's alphabetical. Both these routines write a value to the current cell. There probably isn't a need for two routines to write a numeric value to a cell. The only difference is the resultant length of the data being written. If your application is reading from a random access file, where you know the data value is sure to be a two byte signed integer, use lWriteInt for some space saving. Any applications that need the full range of numbers should call the lWriteNum routine instead, to maintain decimal values. Note that this routine requires an eight byte double precision value though, not a 4 byte single precision value. ERRORS: No file open: errCode returned as -1. Disk Full: errCode returned as 1. DOS errors: Dos error number returned in errCode. 66 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought NAME: lWriteStr COMPATIBILITY: All Releases. CELL POINTER: Advanced to next column. SYNTAX: lWriteStr(dat$, errCode%) dat$ string data to be written. errCode% a non-zero return indicates an error. Writes a label (string data) to the target cell. One item of note is that the currently set label prefix charac- ter, (single quote upon startup, or what you define as default with lSetStrForm) is used if the data string sent to this routine does not include a label prefix character as it's first byte. In other words, if you call this routine with a data string that has a valid label prefix character in the leftmost position, that prefix character will take preference. This routine will not actually write a null ("") cell. If called with a null string, it will advance the internal column pointer to the next column. Strings of ASCII 0's or space (32) charac- ters are written to the file. This is a change from earlier versions of 123-Write. EXAMPLE: CALL lFileOpen("D:\LOTUS\TESTFILE", errCode%) 'default label prefix=" ' " left alignment. 'we're at cell A1, write a title, will be left aligned. CALL lWriteStr("Test worksheet written by 123-Write", errCode%) CALL lLineFeed 'Then write the column headings in row 1... CALL lWriteStr("^Name", errCode%) 'Centered CALL lWriteStr("^Address", errCode%) 'Centered CALL lWriteStr(CHR$(34) + "Balance", errCode%) 'Right justify CALL lLineFeed 'Next the ever popular "=======" repeating character. FOR i = 1 TO 3 CALL lWriteStr("\=", errCode%) NEXT CALL lLineFeed ERRORS: No file open: errCode returned as -1. Disk Full: errCode returned as 1. DOS errors: Dos error number returned in errCode. 67 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought ERRORS.....From Lotus 1-2-3 There really shouldn't be any when you retrieve a worksheet in Lotus, if there is advise me as soon as possible, and include a printout of the code if possible. See below on contacting me. There is a series of real nasty error messages that I encountered developing these routines. Lotus beeps, prints an error message "Worksheet Revision out of date. Press HELP!" is one that comes to mind, there are a number of others too! When you press F1, the help key, a message comes up advising you to call Lotus Customer Support right away. Don't panic, I know I never do. Go over your code and be sure that everything's being called proper- ly, then if need be, write me at the address on the registration form. The @ISAAF and @ISAPP functions were added with 1-2-3 Release 2.2. If they are used with Release 2.0 there will be a "Formula Recalculation Error" message upon loading the file. This is to be expected, loading in a file created by 1-2-3 2.2 would cause the same errors. If you're not sure of the target release of 1-2-3, be careful of which functions you write. 123-Write ERRORS.... The only errors that will be returned by 123-Write are the few codes returned by some of the more frequently used routines. Anything else is blissfully ignored. I'm trying to make a trade off in the number of parameters that have to be passed with the frequency of the routines. If a routine is only to be called once or twice in an entire file write, I felt it was not impor- tant to trap DOS errors. A routine that would be called multiple times, I felt, was the more likely candidate for error traps. BASIC ERRORS: These are yours to trap or ignore or fix. The particular BASIC environment you're running under will dictate what happens. For example, try lFileOpen with a floppy disk file as the target, and the drive door open. In QB or QBX, the environment reports the error, even though the error happens in a Quick Library. From the DOS command line, the run-time library will catch the error before it gets passed back to your program in non-stand alone programs. If the program is compiled with /O, the DOS critical error handler steps in with the "Abort Retry Fail" message. So, I believe that if you have error trapping enabled, errors that occur in the 123-Write routines may cause that trap routine to be called (runtime module should...). Short of having a drive door open, I can't envision any way a user can mess up while running these routines. (Where have I heard those famous last words before?) 68 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought CONTACTING the author: Via Modem: Questions can be answered via a call to The Off Hour Rockers BBS, 201-727-6917 or 201-727-6785 (N81, 9600/2400/1200) a PCBoard BBS. The Sysop was kind enough to give me a conference to ramble away in, so use the J)oin command and select the BASIC conference. Leave a message to TOM VOUGHT, and it will be answered ASAP. Also, Compuserve ID: 70054,1367 (infrequent) Otherwise: Write me at: 5 John Street Morganville, NJ 07751 FINIS: I hope that these routines make your coding easier, and at the same time give you the tools to improve your programs. If you enjoy them, let me know, if there's something you don't like, think should be improved, or want to see included etc, let me know. Anyway, enjoy! << Tom >> 69 123-Write ver 2.0 Copyright (c) 1990, Thomas J. Vought APPENDIX A: Possible error codes returned by DOS. This in not intended as a complete list of the possible error codes that DOS might return, for that I can only refer you to a good DOS reference work. ERROR CODE MEANING ---------- ------- 1 Disk Full. This is not generated by DOS, but by 123-Write when DOS reports fewer bytes written to disk then requested. The file is left open, so a call to lClose should be made before you termi- nate your program. 2 File Not Found. lFileAppend, the path is there, but the file isn't. 3 Path Not Found. lFileOpen or lFileAppend, indicates an invalid drive or path spec. Note that DOS's critical error handler may intercept some errors. A "Drive not ready" error, for example, results in the familiar A)bort R)etry F)ail message. If the user selects Abort, your program may terminate, choosing Fail will result in an error code being passed back to your program by 123-Write (3 in this in- stance). 70 Routine reference. 1-2-3 Release Page LBoolean (col1%, row1%, col2%, row2%, operation%), ALL 18 LCellFunction(dSeg%, dOfs%) MIXED 19 LCellMath(col1%, row1%, col2%, row2%, oper%) ALL 23 LClose ALL 24 LCol (column$) ALL 25 LColFunction (startRow%, endRow%, oper%) MIXED 26 LConstMath(col%, row%, constVal#, oper%) ALL 27 LDate(dat$) ALL 28 LDown(count%) ALL 29 LFileAppend(fileName$, ecode%) ALL 30 LFileOpen(fileName$, errCode%) ALL 32 LGetLoc(whatCol%, whatRow%) ALL 33 LGlobal(item%, switch%) ALL 34 LHideCol(col%, writeFlag%) 2.0+ 35 LLeft(count%) ALL 36 LLineFeed ALL 37 LLink(fileSpecRange$) 2.2 38 LMultiCellMath(dSeg%, dOfs%) ALL 40 LPrintBorders_ ALL 43 (rulc%, rulr%, rlrc%, rlrr%, culc%, culr%, clrc%, clrr%) LPrintMargins(lm%, rm%, tm%, bm%, pageLen%) ALL 44 LPrintRange (ulc%, ulr%, lrc%, lrr%) ALL 45 LPrintSetup (setup$) ALL 46 LPrintTitle (title$, header%) ALL 47 LRange(rangeName$, ulc%, ulr%, lrc%, lrr%) ALL 48 LRight(count%) ALL 49 LRowFunction(startCol%, endCol%, oper%) MIXED 50 LSetCellForm(formatCode%, decimals%) MIXED 51 LSetCol(col%, colWidth%) ALL 53 LSetCols(colWidths%()) ALL 54 LSetDateForm (format%, protect%) MIXED 55 LSetLoc(newCol%, newRow%) ALL 56 LSetStrForm(formatCode%) MIXED 57 LSetTimeForm (format%, protect%) 2.0+ 58 LSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascnd%) ALL 59 LSortRange(ulc%, ulr%, lrc%, lrr%) ALL 60 LTime(timeStr$) 2.0+ 61 LTimeNow 2.0+ 62 LToday ALL 63 LUp(count%) ALL 64 LWriteErr ALL 65 LWriteInt(integerVal%, errCode%) ALL 66 LWriteNum(doubleVal#, errCode%) ALL 66 LWriteStr(dat$, errCode%) ALL 67 71